Pizza Runner: Enhancing Customer Experience and Optimizing Operations
Through Data Analysis and Delivery Network Management
Introduction:
Did you know that over 115 million kilograms of pizza is consumed daily worldwide??? (Well according to Wikipedia anyway…)
Danny was scrolling through his Instagram feed when something really caught his eye - “80s Retro Styling and Pizza Is The Future!”
Danny was sold on the idea, but he knew that pizza alone was not going to help him get seed funding to expand his new Pizza Empire - so he had one more genius idea to combine with it - he was going to Uberize it - and so Pizza Runner was launched!
Danny started by recruiting “runners” to deliver fresh pizza from Pizza Runner Headquarters (otherwise known as Danny’s house) and also maxed out his credit card to pay freelance developers to build a mobile app to accept orders from customers.
Available Data
Because Danny had a few years of experience as a data scientist - he was very aware that data collection was going to be critical for his business’ growth.
He has prepared for us an entity relationship diagram of his database design but requires further assistance to clean his data and apply some basic calculations so he can better direct his runners and optimise Pizza Runner’s operations.
Danny has shared with me 6 key datasets for this case study:
runnerscustomer_ordersrunner_orderspizza_namespizza_recipespizza_toppings
You can inspect the entity relationship diagram and example data below.
Entity Relationship Diagram
Example Datasets
All datasets exist within the dannys_diner database schema
Table 1 : runners
The runners table shows the registration_date for each new runner
| Runner ID | Registration Date |
|---|---|
| 1 | 2021-01-01 |
| 2 | 2021-01-03 |
| 3 | 2021-01-08 |
| 4 | 2021-01-15 |
Table 2: customer_orders
ustomer pizza orders are captured in the customer_orders table with 1 row for each individual pizza that is part of the order.
The pizza_id relates to the type of pizza which was ordered whilst the exclusions are the ingredient_id values which should be removed from the pizza and the extras are the ingredient_id values which need to be added to the pizza.
Note that customers can order multiple pizzas in a single order with varying exclusions and extras values even if the pizza is the same type!
The exclusions and extras columns will need to be cleaned up before using them in your queries.
| Order ID | Customer ID | Pizza ID | Exclusions | Extras | Order Time |
|---|---|---|---|---|---|
| 1 | 101 | 1 | 2021-01-01 18:05:02 | ||
| 2 | 101 | 1 | 2021-01-01 19:00:52 | ||
| 3 | 102 | 1 | 2021-01-02 23:51:23 | ||
| 3 | 102 | 2 | NaN | 2021-01-02 23:51:23 | |
| 4 | 103 | 1 | 4 | 2021-01-04 13:23:46 | |
| 4 | 103 | 1 | 4 | 2021-01-04 13:23:46 | |
| 4 | 103 | 2 | 4 | 2021-01-04 13:23:46 | |
| 5 | 104 | 1 | null | 1 | 2021-01-08 21:00:29 |
Table 3: runner_orders
After each orders are received through the system - they are assigned to a runner - however not all orders are fully completed and can be cancelled by the restaurant or the customer.
The pickup_time is the timestamp at which the runner arrives at the Pizza Runner headquarters to pick up the freshly cooked pizzas. The distance and duration fields are related to how far and long the runner had to travel to deliver the order to the respective customer.
There are some known data issues with this table so be careful when using this in your queries - make sure to check the data types for each column in the schema SQL!
| Order ID | Runner ID | Pickup Time | Distance | Duration | Cancellation |
|---|---|---|---|---|---|
| 1 | 1 | 2021-01-01 18:15:34 | 20km | 32 minutes | |
| 2 | 1 | 2021-01-01 19:10:54 | 20km | 27 minutes | |
| 3 | 1 | 2021-01-03 00:12:37 | 13.4km | 20 mins | NaN |
| 4 | 2 | 2021-01-04 13:53:03 | 23.4 | 40 | NaN |
| 5 | 3 | 2021-01-08 21:10:57 | 10 | 15 | NaN |
Table 4: pizza_names
At the moment - Pizza Runner only has 2 pizzas available the Meat Lovers or Vegetarian!
| Pizza ID | Pizza Name |
|---|---|
| 1 | Meat Lovers |
| 2 | Vegetarian |
Table 5: pizza_recipes
Each pizza_id has a standard set of toppings which are used as part of the pizza recipe.
| Pizza ID | Toppings |
|---|---|
| 1 | 1, 2, 3, 4, 5, 6, 8, 10 |
| 2 | 4, 6, 7, 9, 11, 12 |
Table 6: pizza_toppings
This table contains all of the topping_name values with their corresponding topping_id value
| Topping ID | Topping Name |
|---|---|
| 1 | Bacon |
| 2 | BBQ Sauce |
| 3 | Beef |
| 4 | Cheese |
Credits - Grateful to Danny Ma for Creating this case study
LinkedIn: Danny Ma on LinkedIn
Case Study Questions
They are broken up by area of focus including:
- Pizza Metrics
- Runner and Customer Experience
- Ingredient Optimisation
- Pricing and Ratings
- Bonus DML Challenges (DML = Data Manipulation Language)
Before going into analysis we need to clean and transform the data
Data Cleaning & Transformation
Table - customer_orders
Looking at the customer_orders table below, we can see that there are:
- In the exclusions column, there are missing/ blank spaces ' ' and null values.
- In the extras column, there are missing/ blank spaces ' ' and null values.
| Order ID | Customer ID | Pizza ID | Exclusions | Extras | Order Time |
|---|---|---|---|---|---|
| 1 | 101 | 1 | 2021-01-01 18:05:02 | ||
| 2 | 101 | 1 | 2021-01-01 19:00:52 | ||
| 3 | 102 | 1 | 2021-01-02 23:51:23 | ||
| 3 | 102 | 2 | NaN | 2021-01-02 23:51:23 | |
| 4 | 103 | 1 | 4 | 2021-01-04 13:23:46 | |
| 4 | 103 | 1 | 4 | 2021-01-04 13:23:46 | |
| 4 | 103 | 2 | 4 | 2021-01-04 13:23:46 | |
| 5 | 104 | 1 | null | 1 | 2021-01-08 21:00:29 |
Our course of action to clean the table:
- In pickup_time column, remove nulls and replace with blank space ' '.
- In distance column, remove "km" and nulls and replace with blank space ' '.
- In duration column, remove "minutes", "minute" and nulls and replace with blank space ' '.
- In cancellation column, remove NULL and null and and replace with blank space ' '.
After cleaning this is how it looks
| Order ID | Customer ID | Pizza ID | Exclusions | Extras | Order Time |
|---|---|---|---|---|---|
| 1 | 101 | 1 | 2021-01-01 18:05:02 | ||
| 2 | 101 | 1 | 2021-01-01 19:00:52 | ||
| 3 | 102 | 1 | 2021-01-02 23:51:23 | ||
| 3 | 102 | 2 | 2021-01-02 23:51:23 | ||
| 4 | 103 | 1 | 4 | 2021-01-04 13:23:46 | |
| 4 | 103 | 1 | 4 | 2021-01-04 13:23:46 | |
| 4 | 103 | 2 | 4 | 2021-01-04 13:23:46 | |
| 5 | 104 | 1 | 1 | 2021-01-08 21:00:29 |
Table - runners_orders
Looking at the runner_orders table below, we can see that there are:
- In the exclusions column, there are missing/ blank spaces ' ' and null values.
- In the extras column, there are missing/ blank spaces ' ' and null values
| Order ID | Runner ID | Pickup Time | Distance | Duration | Cancellation |
|---|---|---|---|---|---|
| 1 | 1 | 2021-01-01 18:15:34 | 20km | 32 minutes | |
| 2 | 1 | 2021-01-01 19:10:54 | 20km | 27 minutes | |
| 3 | 1 | 2021-01-03 00:12:37 | 13.4km | 20 mins | NaN |
| 4 | 2 | 2021-01-04 13:53:03 | 23.4 | 40 | NaN |
| 5 | 3 | 2021-01-08 21:10:57 | 10 | 15 | NaN |
Our course of action to clean the table:
- In pickup_time column, remove nulls and replace with blank space ' '.
- In distance column, remove "km" and nulls and replace with blank space ' '.
- In duration column, remove "minutes", "minute" and nulls and replace with blank space ' '.
- In cancellation column, remove NULL and null and and replace with blank space ' '.
Then, we alter the pickup_time, distance and duration columns to the correct data type.
After cleaning this is how it looks
| Order ID | Runner ID | Pickup Time | Distance | Duration | Cancellation |
|---|---|---|---|---|---|
| 1 | 1 | 2021-01-01 18:15:34 | 20 | 32 | |
| 2 | 1 | 2021-01-01 19:10:54 | 20 | 27 | |
| 3 | 1 | 2021-01-03 00:12:37 | 13.4 | 20 | |
| 4 | 2 | 2021-01-04 13:53:03 | 23.4 | 40 | |
| 5 | 3 | 2021-01-08 21:10:57 | 10 | 15 |
A. Pizza Metrics
1. How many pizzas were ordered?
Total of 14 pizzas were ordered.
Detailed Explanation
Click to view
Inventory management:
Understanding the number of pizzas ordered helps the business manage its inventory effectively. It allows them to estimate the quantity of ingredients and supplies required to meet the demand and avoid shortages or wastage.Production planning:
The order count helps in planning the production of pizzas. It provides insights into the volume of orders, allowing the business to allocate resources and schedule production shifts accordingly. This helps maintain efficiency and ensures timely order fulfillment.Sales analysis:
The total count of pizzas ordered provides valuable data for sales analysis. It allows the business to assess the popularity of pizzas and identify trends in customer preferences. This information can guide menu optimization, promotional campaigns, and product development strategies.Revenue forecasting:
By knowing the number of pizzas ordered, the business can forecast its revenue. It helps in estimating sales and projecting future financial performance. This information is crucial for financial planning, budgeting, and setting sales targets.Customer satisfaction:
Monitoring the order count provides an indication of customer satisfaction. If the number of orders is high, it suggests that customers enjoy the pizzas and are likely to be satisfied with their dining experience. This information can help identify popular menu items and ensure consistent quality to drive customer loyalty.Short Explanation (Summary)
Click to view
Knowing the total count of pizzas ordered helps the business with inventory management, production planning, sales analysis, revenue forecasting, and customer satisfaction. It enables efficient resource allocation, data-driven decision-making, and overall business optimization.
2. How many unique customer orders were made?
Total of 10 pizzas were ordered.
Detailed Explanation
Click to view
Sales analysis:
The count of unique orders provides insights into the number of distinct transactions made by customers. It helps the business analyze sales performance and identify patterns or trends in customer behavior. This information can guide pricing strategies, promotional campaigns, and overall sales planning.Customer engagement:
Understanding the count of unique orders helps assess customer engagement and loyalty. It indicates the number of customers who have placed orders, allowing the business to identify repeat customers and measure their satisfaction. This data can support customer retention efforts and help tailor marketing strategies to enhance customer loyalty.Demand forecasting:
The count of unique orders aids in demand forecasting. By analyzing historical order patterns, the business can estimate future sales and plan inventory levels accordingly. It ensures sufficient stock availability to meet customer demand while avoiding overstocking or shortages.Operational efficiency:
Monitoring the count of unique orders helps the business assess its operational efficiency. It allows for evaluating order processing times, delivery performance, and overall customer service. This data helps identify areas for improvement and streamline operations to enhance customer satisfaction and optimize resource allocation.Revenue tracking:
Tracking the count of unique orders provides a basis for revenue tracking and financial analysis. It helps measure sales growth, evaluate pricing strategies, and assess the effectiveness of promotional efforts. This information is essential for budgeting, financial planning, and identifying opportunities for revenue optimization.Short Explanation (Summary)
Click to view
Knowing the count of unique orders helps the business analyze sales performance, assess customer engagement, forecast demand, improve operational efficiency, and track revenue. It enables data-driven decision-making, enhances customer satisfaction, and supports overall business planning and performance evaluation.
3. How many successful orders were delivered by each runner?
The number of successful deliveires from each runner:
- Runner 1 has 4 successful delivered orders.
- Runner 2 has 3 successful delivered orders.
- Runner 3 has 1 successful delivered order.
Detailed Explanation
Click to view
Performance evaluation:
The count of successful delivered orders allows the business to evaluate the performance of individual runners. It helps identify high-performing runners who consistently fulfill orders successfully and meet customer expectations. This information can be used for recognition, incentives, and performance management.Resource allocation:
Understanding the number of successful delivered orders for each runner helps in resource allocation and scheduling. The business can assign more orders to runners who have a track record of successful deliveries, ensuring efficient and timely order fulfillment. It optimizes the allocation of resources such as delivery personnel and vehicles.Customer satisfaction:
Monitoring the count of successful delivered orders helps gauge customer satisfaction. A higher number of successful orders indicates that runners are meeting delivery expectations and ensuring a positive customer experience. It helps identify areas for improvement and ensure consistent service quality.Operational efficiency:
By analyzing the count of successful delivered orders, the business can identify potential bottlenecks or inefficiencies in the delivery process. It helps identify areas for improvement, such as optimizing delivery routes, providing additional training or support to runners, or streamlining order management systems. This improves overall operational efficiency and customer satisfaction.Quality control:
Tracking the count of successful delivered orders helps the business maintain quality control standards. It provides insights into the reliability and consistency of runners in delivering orders successfully. It enables the business to identify any issues or deviations from expected service levels and take corrective measures to ensure quality standards are met.Short Explanation (Summary)
Click to view
Knowing the count of successful delivered orders for each runner helps evaluate performance, allocate resources efficiently, ensure customer satisfaction, improve operational efficiency, and maintain quality control. It enables data-driven decision-making and supports overall business optimization.
4. How many of each type of pizza was delivered?
There are 9 delivered Meatlovers pizzas and 3 Vegetarian pizzas.
Detailed Explanation
Click to view
Menu optimization:
The count of delivered pizzas provides insights into the popularity of different pizza types. It helps the business identify the most popular pizzas and optimize its menu accordingly. This information can guide decisions on menu offerings, pricing, and promotional strategies to maximize customer satisfaction and revenue.Inventory management:
Understanding the count of delivered pizzas for each type helps with inventory management. It allows the business to estimate the demand for specific ingredients and toppings associated with popular pizzas. This helps in planning and maintaining optimal inventory levels, reducing waste, and ensuring smooth operations.Supplier management:
The information on delivered pizzas supports effective supplier management. By analyzing the count of pizzas for each type, the business can identify suppliers who consistently provide quality ingredients for popular pizzas. This helps in fostering good supplier relationships and ensuring a reliable supply chain.Sales analysis:
The count of delivered pizzas enables sales analysis. It helps the business track the performance of different pizza types and identify trends in customer preferences. This information assists in making data-driven decisions regarding promotions, discounts, and targeted marketing campaigns to drive sales and increase customer engagement.Customer satisfaction:
Monitoring the count of delivered pizzas for each type allows the business to assess customer satisfaction. By identifying popular pizzas, the business can ensure consistent quality and timely delivery, enhancing the overall dining experience. Satisfied customers are more likely to become repeat customers and recommend the business to others.Short Explanation (Summary)
Click to view
Knowing the count of delivered pizzas for each type helps optimize the menu, manage inventory efficiently, analyze sales performance, foster supplier relationships, and enhance customer satisfaction. It enables data-driven decisions and improves overall business performance.
5. How many Vegetarian and Meatlovers were ordered by each customer?
- Customer 101 ordered 2 Meatlovers pizzas and 1 Vegetarian pizza.
- Customer 102 ordered 2 Meatlovers pizzas and 2 Vegetarian pizzas.
- Customer 103 ordered 3 Meatlovers pizzas and 1 Vegetarian pizza.
- Customer 104 ordered 3 Meatlovers pizza.
- Customer 105 ordered 1 Vegetarian pizza.
Detailed Explanation
Click to view
Customer segmentation:
The information allows the business to segment customers based on their pizza preferences. It helps identify patterns and preferences among different customer groups, enabling targeted marketing strategies and personalized offers.Menu optimization:
Understanding the count of pizzas ordered for each type helps in menu optimization. It allows the business to identify the most popular pizzas and ensure their availability and quality. It also helps in identifying potential gaps in the menu or opportunities for introducing new pizza varieties based on customer preferences.Demand forecasting:
The count of pizzas ordered by each customer helps in demand forecasting. By analyzing order patterns and trends, the business can estimate future demand for specific pizza types. This information aids in production planning, inventory management, and ensuring sufficient stock levels.Upselling and cross-selling:
Analyzing the count of pizzas ordered by each customer allows the business to identify upselling and cross-selling opportunities. For example, customers who frequently order Meatlovers pizzas may be more receptive to offers or promotions related to other meat-based pizzas or complementary items like side dishes or beverages.Customer satisfaction and retention:
Monitoring the count of pizzas ordered helps gauge customer satisfaction. It allows the business to identify loyal customers who consistently order their preferred pizzas. By providing personalized offers and ensuring quality and timely deliveries, the business can enhance customer satisfaction and foster long-term loyalty.Short Explanation (Summary)
Click to view
Knowing the count of pizzas ordered by each customer helps with customer segmentation, menu optimization, demand forecasting, upselling/cross-selling, and enhancing customer satisfaction and retention. It enables personalized marketing strategies and improves overall business performance.
6. What was the maximum number of pizzas delivered in a single order?
Maximum number of pizza delivered in a single order is 3 pizzas.
Detailed Explanation
Click to view
Resource Allocation:
Understanding the maximum number of pizzas delivered in a single order helps in optimizing resource allocation. The business can ensure they have enough ingredients, staff, and delivery capacity to handle larger orders efficiently.Operational Efficiency:
By identifying the maximum number of pizzas in an order, the business can streamline their operations and processes to handle such orders more effectively. This may involve optimizing kitchen workflows, delivery routes, and packaging methods to maintain the quality and timeliness of larger orders.Upselling and Promotions:
Knowing the maximum order size allows the business to design targeted upselling strategies and promotional offers. They can create special deals or bundle offers that encourage customers to order more pizzas per order, thereby increasing the average order value and revenue.Customer Satisfaction:
Large orders indicate customers' trust and satisfaction with the business. By ensuring smooth and reliable delivery of larger orders, the business can enhance customer satisfaction and loyalty. They can also gather feedback from customers who place large orders to understand their preferences and improve their overall experience.Menu Planning:
The business can analyze the toppings, pizza types, and combinations that are commonly ordered in larger quantities. This information can guide menu planning and help the business introduce new pizza options or variations to cater to customer preferences and increase sales.Short Explanation (Summary)
Click to view
Knowing the maximum number of pizzas delivered in a single order helps the business optimize resources, improve operational efficiency, design targeted promotions, enhance customer satisfaction, and plan menu offerings.
7. For each customer, how many delivered pizzas had at least 1 change and how many had no changes?
- 3 customers had at least one change in their pizza
- 3 pizzas ordered by customers had no change at all.
Detailed Explanation
Click to view
Personalized Offerings:
Recognizing that some customers prefer customized pizzas with changes in toppings (exclusions or extras) allows the business to offer personalized options. They can create special menu items or customizable pizza options to cater to these preferences, providing a more tailored experience for customers.Upselling Opportunities:
Identifying customers who are open to changes in their pizza orders presents upselling opportunities. The business can suggest additional toppings or premium ingredients as upsell options, increasing the average order value and boosting revenue.Customer Retention:
By catering to customers' preferences for customized pizzas, the business can enhance customer satisfaction and loyalty. Offering flexibility and accommodating changes in toppings can make customers feel valued, increasing the likelihood of repeat orders and fostering long-term relationships.Menu Optimization:
Analyzing customers' preferences for original recipe pizzas versus customized ones helps in menu optimization. The business can evaluate the popularity of different toppings and combinations, adjusting their menu offerings accordingly to align with customer preferences and streamline operations.Marketing and Promotions:
Utilizing the knowledge of customers' preferences for customized pizzas, the business can create targeted marketing campaigns and promotions. They can highlight the option for customization, enticing customers who enjoy making changes to their pizza orders and attracting new customers with tailored offerings.Short Explanation (Summary)
Click to view
Understanding customers' preferences for customized pizzas helps the business offer personalized options, increase upselling opportunities, enhance customer satisfaction and loyalty, optimize menu offerings, and create targeted marketing campaigns.
8. How many pizzas were delivered that had both exclusions and extras?
- Only 1 Pizzas had both exclusions and extra toppings in theie pizza.
Detailed Explanation
Click to view
Customization Trends:
Understanding that a significant number of customers prefer customized pizzas with both exclusions and extras provides valuable insights into the customization trends. The business can analyze the specific toppings that are commonly excluded or added as extras, helping them tailor their menu offerings and create targeted promotions to cater to these preferences.Upselling Opportunities:
Recognizing the popularity of pizzas with both exclusions and extras presents upselling opportunities. The business can highlight premium toppings or unique combinations as additional options for customers to further customize their pizzas. This can lead to increased average order value and revenue.Menu Optimization:
The information about pizzas with exclusions and extras helps the business optimize its menu. They can evaluate the popularity of different customization options and consider adding pre-designed specialty pizzas that align with customer preferences. This can streamline operations, reduce errors in customization, and enhance overall customer satisfaction.Customer Satisfaction:
Offering the flexibility for customers to personalize their pizzas with both exclusions and extras can greatly enhance their satisfaction. By consistently delivering customized orders with high accuracy, the business can build strong customer loyalty and positive word-of-mouth referrals.Data-Driven Decision Making:
The data on pizzas with exclusions and extras can be used for data-driven decision making. The business can track and analyze the impact of customization on customer satisfaction, order value, and overall business performance. This information can guide future strategies, marketing campaigns, and menu innovations to continuously meet customer demands.Short Explanation (Summary)
Click to view
Understanding the presence of both exclusions and extras in customer orders provides valuable insights for menu planning, customer satisfaction, upselling opportunities, and data-driven decision making. By leveraging this information effectively, the business can enhance customer experience, drive revenue growth, and stay competitive in the market.
9. What was the total volume of pizzas ordered for each hour of the day?
- Highest volume of pizza ordered is at 13 (1:00 pm), 18 (6:00 pm), 21 (9:00 pm) and 23 (11:00 pm).
- Lowest volume of pizza ordered is at 11 (11:00 am) and 19 (7:00 pm).
Detailed Explanation
Click to view
Staffing and Resource Allocation:
Knowing the peak hours of pizza orders allows the business to efficiently allocate staff and resources. They can schedule more employees during high-volume hours to ensure prompt order preparation and delivery, reducing customer wait times and enhancing overall service quality.Inventory Management:
Understanding the busy and slow periods helps the business optimize inventory management. They can adjust ingredient stock levels and production quantities to align with the demand fluctuations throughout the day, minimizing waste and ensuring sufficient supply during peak hours.Delivery Optimization:
Identifying the busiest hours for pizza orders enables the business to optimize their delivery operations. They can plan delivery routes and assign drivers strategically to ensure timely deliveries during high-demand periods, improving customer satisfaction and retention.Marketing and Promotions:
Utilizing the knowledge of peak and off-peak hours, the business can create targeted marketing campaigns and promotions. They can offer time-specific deals or discounts during slower periods to stimulate demand and attract more customers during those hours.Menu Planning:
The information about high and low volume hours can guide menu planning. The business can introduce special menu items or promotions that cater to customer preferences during peak hours, offering attractive choices and enhancing the overall customer experience.Short Explanation (Summary)
Click to view
Analyzing the volume of pizza orders by the hour helps the business optimize staffing, inventory, and delivery operations. It also enables targeted marketing and promotions, as well as menu planning based on customer preferences during peak hours.
10. What was the volume of orders for each day of the week?
- There are 5 pizzas ordered on Sunday and Wednesday.
- There are 3 pizzas ordered on Monday.
- There is 1 pizza ordered on Tuesday.
Detailed Explanation
Click to view
Demand Forecasting:
Understanding the pizza order volume for each day of the week helps the business forecast future demand. They can anticipate busy and slow days, allowing for better planning of resources, such as staffing and inventory, to efficiently meet customer demands.Menu Planning and Specials:
By identifying the popular and slower days for pizza orders, the business can tailor their menu offerings and promotions accordingly. They can introduce special menu items or targeted promotions on slower days to stimulate demand and attract more customers.Operational Efficiency:
Knowing the order patterns by day of the week enables the business to optimize their operations. They can adjust staffing levels, production quantities, and delivery schedules based on the expected order volume for each day, ensuring smooth operations and minimizing waste.Marketing Strategies:
The information about order volume by day of the week helps in developing effective marketing strategies. The business can target specific days with promotions, discounts, or loyalty programs to incentivize customers and drive sales during slower periods.Customer Engagement:
Recognizing the popular days for pizza orders allows the business to engage with customers and create a sense of anticipation. They can plan special events, themed offers, or limited-time deals on popular days to foster customer loyalty and enhance the overall dining experience.Short Explanation (Summary)
Click to view
Analyzing pizza orders by day of the week helps the business forecast demand, optimize operations, plan menus, and tailor marketing strategies. It enables efficient resource allocation, targeted promotions, and enhanced customer engagement for improved business performance and customer satisfaction.
B. Runner and Customer Experience
1. How many runners signed up for each 1 week period? (i.e. week starts 2021-01-01)
- On Week 1 of Jan 2021, 2 new runners signed up.
- On Week 2 and 3 of Jan 2021, 1 new runner signed up.
Detailed Explanation
Click to view
Performance tracking:
Tracking weekly sign-ups allows the business to monitor its growth and track the effectiveness of marketing and recruitment efforts. It provides insights into the success of strategies aimed at attracting new runners and helps identify trends or patterns in registration.Capacity planning:
Understanding the number of new runners signing up each week helps the business in capacity planning. It helps estimate the resources required to accommodate new runners, such as providing adequate training, allocating staff, and managing logistics.Revenue forecasting:
The count of new runner sign-ups provides an indicator of potential revenue growth. By analyzing registration trends, the business can forecast future revenue, plan for expenses, and make informed financial decisions.Marketing and recruitment strategies:
Knowing the weekly sign-up count helps assess the impact of marketing and recruitment strategies. It helps identify peak periods or successful campaigns and provides insights into the target audience's response to various marketing channels or messaging. This information can guide future marketing efforts and optimize recruitment strategies.Member engagement:
Monitoring the number of new runners signing up each week helps the business focus on member engagement. By providing a positive onboarding experience for new runners, the business can increase member retention and satisfaction. This may include personalized welcome messages, orientation sessions, or exclusive offers for new members.Short Explanation (Summary)
Click to view
Knowing the count of new runner sign-ups each week helps track performance, plan capacity, forecast revenue, evaluate marketing strategies, and enhance member engagement. It enables data-driven decision-making and supports business growth and optimization.
2. What was the average time in minutes it took for each runner to arrive at the Pizza Runner HQ to pickup the order?
The average time it took for each runner to arrive at the Pizza Runner HQ to pickup the order are:
- Runner 1 took 14 minutes of average time to pickup the order from HQ
- Runner 2 took 19 minutes of average time to pickup the order from HQ
- Runner 3 took 10 minutes of average time to pickup the order from HQ
Detailed Explanation
Click to view
Operational efficiency:
By understanding the average pickup time, the business can identify runners who consistently perform well in terms of quick order pickups. This information enables the business to recognize efficient runners and optimize its delivery operations for faster and more reliable service.Customer satisfaction:
Timely order pickups contribute to better customer satisfaction. By monitoring the average pickup time, the business can ensure that orders are promptly collected and dispatched for delivery. This enhances the overall customer experience and increases the likelihood of repeat orders and positive reviews.Performance evaluation:
Analyzing the average pickup time allows the business to evaluate the performance of individual runners. It provides insights into their efficiency and adherence to pickup schedules. This information can be used for performance reviews, training, and identifying areas for improvement.Service level improvement:
Understanding the average pickup time helps the business identify potential bottlenecks or areas where the pickup process can be optimized. It may involve streamlining order management, adjusting pickup schedules, or providing additional support to runners to improve overall service levels.Operational planning:
The average pickup time data aids in operational planning. It helps in assigning suitable runners for specific delivery routes or time slots based on their pickup efficiency. This supports better resource allocation and ensures timely deliveries.Short Explanation (Summary)
Click to view
Knowing the average pickup time for each runner helps improve operational efficiency, enhance customer satisfaction, evaluate performance, identify areas for improvement, and enable effective operational planning. It supports the business in delivering faster and more reliable service, leading to enhanced customer loyalty and business growth.
3. Is there any relationship between the number of pizzas and how long the order takes to prepare?
The analysis shows that there is a relationship between the number of pizzas in an order and the time it takes to prepare.
- On average, a single pizza order takes 12 minutes to prepare.
- An order with 3 pizzas takes 29 minutes at an average of 9.5 minutes per pizza.
- It takes 19 minutes to prepare an order with 2 pizzas which is 9.5 minutes per pizza. This suggests that preparing multiple pizzas in a single order is more efficient in terms of time per pizza compared to individual orders.
Detailed Explanation
Click to view
Operational efficiency:
By understanding the average pickup time, the business can identify runners who consistently perform well in terms of quick order pickups. This information enables the business to recognize efficient runners and optimize its delivery operations for faster and more reliable service.Customer satisfaction:
Timely order pickups contribute to better customer satisfaction. By monitoring the average pickup time, the business can ensure that orders are promptly collected and dispatched for delivery. This enhances the overall customer experience and increases the likelihood of repeat orders and positive reviews.Performance evaluation:
Analyzing the average pickup time allows the business to evaluate the performance of individual runners. It provides insights into their efficiency and adherence to pickup schedules. This information can be used for performance reviews, training, and identifying areas for improvement.Service level improvement:
Understanding the average pickup time helps the business identify potential bottlenecks or areas where the pickup process can be optimized. It may involve streamlining order management, adjusting pickup schedules, or providing additional support to runners to improve overall service levels.Operational planning:
The average pickup time data aids in operational planning. It helps in assigning suitable runners for specific delivery routes or time slots based on their pickup efficiency. This supports better resource allocation and ensures timely deliveries.Short Explanation (Summary)
Click to view
Knowing the average pickup time for each runner helps improve operational efficiency, enhance customer satisfaction, evaluate performance, identify areas for improvement, and enable effective operational planning. It supports the business in delivering faster and more reliable service, leading to enhanced customer loyalty and business growth.
4. What was the average distance travelled for each customer?
Customer 104 stays the nearest to Pizza Runner HQ at average distance of 10km, whereas Customer 105 stays the furthest at 25km.
Detailed Explanation
Click to view
Delivery optimization:
Understanding the average distance allows the business to optimize delivery routes and assign runners more efficiently. Customers located closer to the HQ can be served more quickly, reducing delivery times and improving customer satisfaction. It also helps in planning the logistics of delivery operations.Resource allocation:
By knowing the average distance, the business can allocate resources such as delivery personnel and vehicles more effectively. It helps determine the number of runners needed in specific areas based on customer density and distance. This ensures optimal resource utilization and cost management.Delivery fees:
The average distance information helps in setting fair and competitive delivery fees. Customers residing further away from the HQ may incur higher delivery charges due to the increased distance. This allows the business to implement a pricing structure that considers the cost of longer deliveries while remaining competitive in the market.Customer targeting:
The average distance data enables the business to identify potential customer segments based on proximity to the HQ. It helps in developing targeted marketing strategies, promotions, or loyalty programs for customers within specific distance ranges. This can lead to increased customer engagement and loyalty.Expansion planning:
Understanding the average distance between customers and the HQ provides insights for potential expansion plans. It helps in evaluating the feasibility of opening new branches or satellite locations in areas with high customer density but far distances. This data supports informed decision-making and business growth strategies.Short Explanation (Summary)
Click to view
Knowing the average distance between customers and the Pizza Runner HQ assists in delivery optimization, resource allocation, pricing strategies, customer targeting, and expansion planning. It enables the business to enhance operational efficiency, customer satisfaction, and overall business performance.
5. What was the difference between the longest and shortest delivery times for all orders?
The difference between longest (40 minutes) and shortest (10 minutes) delivery time for all orders is 30 minutes.
Detailed Explanation
Click to view
Performance evaluation:
The difference in delivery times provides insights into the efficiency and consistency of the delivery process. It helps the business assess the performance of runners and identify areas where improvements can be made. By minimizing the delivery time variance, the business can strive for more reliable and predictable delivery service.Customer satisfaction:
Consistency in delivery times contributes to better customer satisfaction. By reducing the difference between the longest and shortest delivery times, the business can ensure a more consistent experience for customers. This helps build trust and enhances the reputation of the business, leading to increased customer loyalty and positive word-of-mouth.Operational efficiency:
Analyzing the difference in delivery times helps identify factors that contribute to longer or shorter delivery durations. It enables the business to pinpoint areas where operational efficiency can be improved, such as optimizing delivery routes, providing additional training to runners, or streamlining order processing. This leads to smoother operations and reduced delivery times overall.Service level agreements:
The difference in delivery times provides insights into meeting service level agreements (SLAs). If the business has specific delivery time commitments, monitoring the difference helps ensure compliance with those agreements. It enables the business to assess its performance against SLAs and take corrective actions if necessary.Process optimization:
Understanding the difference in delivery times helps the business identify potential bottlenecks or inefficiencies in the delivery process. It allows for process optimization by implementing measures to minimize variations and streamline operations. This can include improving coordination between runners and the HQ, leveraging technology for real-time tracking, or implementing automation to reduce delivery time variability.Short Explanation (Summary)
Click to view
Knowing the difference between the longest and shortest delivery times helps evaluate performance, enhance customer satisfaction, improve operational efficiency, adhere to service level agreements, and optimize delivery processes. It supports the business in delivering consistent and reliable service, ultimately leading to improved customer experiences and business growth.
6. What was the average speed for each runner for each delivery and do you notice any trend for these values?
Calculate average speed - (Average speed = Distance in km / Duration in hour)
- Runner 1’s average speed runs from 37.5km/h to 60km/h.
- Runner 2’s average speed runs from 35.1km/h to 93.6km/h. Owner should investigate Runner 2 as the average speed has a 266% fluctuation rate! (93.6/35.1*100)
- Runner 3’s average speed is 40km/h
Detailed Explanation
Click to view
Operational efficiency:
By understanding the average pickup time, the business can identify runners who consistently perform well in terms of quick order pickups. This information enables the business to recognize efficient runners and optimize its delivery operations for faster and more reliable service.Customer satisfaction:
Timely order pickups contribute to better customer satisfaction. By monitoring the average pickup time, the business can ensure that orders are promptly collected and dispatched for delivery. This enhances the overall customer experience and increases the likelihood of repeat orders and positive reviews.Performance evaluation:
Analyzing the average pickup time allows the business to evaluate the performance of individual runners. It provides insights into their efficiency and adherence to pickup schedules. This information can be used for performance reviews, training, and identifying areas for improvement.Service level improvement:
Understanding the average pickup time helps the business identify potential bottlenecks or areas where the pickup process can be optimized. It may involve streamlining order management, adjusting pickup schedules, or providing additional support to runners to improve overall service levels.Operational planning:
The average pickup time data aids in operational planning. It helps in assigning suitable runners for specific delivery routes or time slots based on their pickup efficiency. This supports better resource allocation and ensures timely deliveries.Short Explanation (Summary)
Click to view
Knowing the average pickup time for each runner helps improve operational efficiency, enhance customer satisfaction, evaluate performance, identify areas for improvement, and enable effective operational planning. It supports the business in delivering faster and more reliable service, leading to enhanced customer loyalty and business growth.
7. What is the successful delivery percentage for each runner?
- Runner 1 has 100% successful delivery.
- Runner 2 has 75% successful delivery.
- Runner 3 has 50% successful delivery.
Detailed Explanation
Click to view
Performance Evaluation:
The success percentage allows the business to evaluate each runner's performance. Runners with higher success percentages are likely to be more reliable and efficient in delivering orders. This evaluation can help the business identify top-performing runners and reward or incentivize them accordingly.Quality Control:
By tracking the success percentage over time, the business can monitor the consistency of each runner's performance. If a runner's success percentage drops significantly, it may indicate an issue that needs to be addressed, such as delayed deliveries, damaged goods, or customer complaints. Timely identification of such issues allows the business to take corrective measures, such as providing additional training or investigating potential problems.Customer Satisfaction:
Successful deliveries contribute to customer satisfaction. By analyzing the success percentages, the business can identify areas where improvements are needed to enhance customer experience. For example, if a runner consistently has a low success percentage, it might be necessary to review their route planning, delivery instructions, or communication with customers to minimize delivery failures and increase customer satisfaction.Resource Allocation:
Understanding the success percentages of individual runners helps with resource allocation and workload distribution. The business can assign more deliveries to runners with higher success percentages, knowing they are likely to handle the orders effectively. On the other hand, runners with lower success percentages might require additional support or training to improve their performance.Business Growth:
Monitoring and improving the success percentages of runners can contribute to the overall growth of the business. Reliable and successful deliveries enhance the business's reputation, leading to increased customer trust and loyalty. Satisfied customers are more likely to recommend the service to others, resulting in new customers and business expansion.Short Explanation (Summary)
Click to view
Knowing the success percentages of individual runners, the business can evaluate their performance, ensure quality control, improve customer satisfaction, optimize resource allocation, and drive business growth. It helps identify top performers, address issues, enhance customer experience, and make informed decisions for operational efficiency.
Conclusion
In conclusion, the data analysis of Pizza Runner's database has provided valuable insights into the operations and customer experience of the pizza delivery service. By examining various pizza metrics, we gained a deeper understanding of the ordering patterns and preferences of customers. We discovered the total number of pizzas ordered, the unique customer orders, and the popularity of different pizza types among customers. Additionally, we explored the frequency of changes made to pizzas, such as exclusions and extras, and identified the pizzas that had both exclusions and extras.
Furthermore, we delved into the runner and customer experience aspects of Pizza Runner. We analyzed the number of runners signing up over specific periods, examined the average time taken by runners to arrive at the Pizza Runner HQ for order pickup, and explored the relationship between the number of pizzas and the time required to prepare an order. Additionally, we investigated the average distance traveled by customers and identified the difference between the longest and shortest delivery times.
The analysis also allowed us to evaluate runner performance. We calculated the average speed for each runner and determined the successful delivery percentage for each runner. These insights can assist Pizza Runner in optimizing their operations, improving delivery efficiency, and enhancing the overall customer experience.
By leveraging the power of data analysis, Pizza Runner can make data-driven decisions to streamline their processes, enhance delivery services, and tailor their offerings to better suit customer preferences. This holistic approach will enable Pizza Runner to establish a strong foothold in the competitive pizza delivery market and provide a seamless and delightful experience to their customers.
As Pizza Runner continues to grow and expand its operations, regular data analysis and optimization will be key to maintaining customer satisfaction, maximizing efficiency, and driving business success.